"""
Case Type   : SQL
Case Name   : 含长字段的列存表，经过DML后vacuum deltamerge
Create At   : 2022/8/15
Owner       : haomeng802
Description :
    1.开启enable_delta_store
    2.创建测试表
    3.检查列存表相关对象
    4.插入数据到列存表，总长度大于8k
    5.对列存表进行vacuum deltamerge
    6.查询该列存表 select * from test_oid_rep;
    7.清理环境 drop table  test_oid_rep;
Expect      :
    1.开启enable_delta_store成功
    2.创建测试表成功
    3.检查列存表相关对象成功
    4.插入数据到列存表，总长度大于8k成功
    5.对列存表进行vacuum deltamerge成功
    6.查询该列存表成功
    7.清理环境成功
History     :
"""

import os
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Common import Common


class Sql_Operation(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('dbuser')
        self.com = Common()
        self.tb_name = 't_set_case0155'
        self.param = 'enable_delta_store'

    def test_explain(self):
        text = '-----step1:开启enable_delta_store;expect:成功-----'
        self.log.info(text)

        set_param = self.comsh.execute_gsguc('set',
                                             self.constant.GSGUC_SUCCESS_MSG,
                                             f'{self.param}=on')
        self.assertTrue(set_param, '执行失败' + text)

        restart = self.comsh.restart_db_cluster()
        self.log.info(restart)
        status_msg = self.comsh.get_db_cluster_status()
        self.assertTrue("Normal" or "Degrade" in status_msg)

        show_msg = self.com.show_param(f'{self.param}')
        self.assertIn("on", show_msg, '参数查看失败异常')

        text = '-----step2:创建测试表;expect:成功-----'
        self.log.info(text)
        sql_cmd1 = self.comsh.execut_db_sql(
            f'drop table if exists {self.tb_name};'
            f'create table {self.tb_name} (id int, a varchar(4000),'
            f'b varchar(4000), c varchar(4000), d varchar(4000)) with '
            f'(orientation=column, deltarow_threshold=10);')
        self.log.info(sql_cmd1)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_cmd1,
                      '执行失败' + text)

        text = '-----step3:检查列存表相关对象;expect:成功-----'
        self.log.info(text)
        sql_cmd2 = self.comsh.execut_db_sql(
            f"select a.relname, a.oid, a.reldeltarelid, "
            f"'cstore.'||b.relname deltaname, b.reltoastrelid, "
            f"'pg_toast.'||c.relname toastname from pg_class a, "
            f"pg_class b,pg_class c where a.relname = '{self.tb_name}' "
            f"and a.reldeltarelid = b.oid and b.reltoastrelid = c.oid;")
        self.log.info(sql_cmd2)
        self.assertIn('1 row', sql_cmd2, '执行失败' + text)

        text = '-----step4:插入数据到列存表，总长度大于8k,expect:成功-----'
        self.log.info(text)
        sql_cmd3 = self.comsh.execut_db_sql(f'''
            declare
            bigstr text;
            begin
            for i in 0..100 loop
            bigstr = bigstr||' '||md5(i);
            end loop;
        
            for i in 0..100 loop
            insert into {self.tb_name}
            values(i, bigstr||md5(i+1), bigstr||md5(i+2), bigstr||md5(i+3),
            bigstr||md5(i+4));
            end loop;        
            end;''')
        self.log.info(sql_cmd3)
        self.assertIn(self.constant.CREATE_ANONYMOUS_BLOCK_SUCCESS_MSG, sql_cmd3, '执行失败:' + text)

        text = '-----step5:对列存表进行vacuum deltamerge;expect:成功-----'
        self.log.info(text)
        sql_cmd4 = self.comsh.execut_db_sql(
            f'vacuum deltamerge {self.tb_name};')
        self.log.info(sql_cmd4)
        self.assertIn('DELTA MERGE',sql_cmd4, '执行失败' + text)

        text = '-----step6:查询该列存表成功;expect:成功-----'
        self.log.info(text)
        sql_cmd4 = self.comsh.execut_db_sql(
            f'select count(*) from {self.tb_name};')
        self.log.info(sql_cmd4)
        self.assertIn('101',sql_cmd4.splitlines()[-2].strip(), '执行失败' + text)

    def tearDown(self):
        text = '---step7:清理环境;expect:清理成功---'
        self.log.info(text)
        sql_cmd5 = self.comsh.execut_db_sql(f'drop table {self.tb_name};')
        self.log.info(sql_cmd5)
        reset = self.comsh.execute_gsguc('set', self.constant.GSGUC_SUCCESS_MSG,
                                         f'{self.param}=off')
        self.assertTrue(reset, '参数设置失败')
        restart = self.comsh.restart_db_cluster()
        self.log.info(restart)
        status = self.comsh.get_db_cluster_status()
        self.log.info(status)
        param = self.com.show_param(f'{self.param}')

        self.assertIn(self.constant.DROP_TABLE_SUCCESS, sql_cmd5,
                      '执行失败' + text)
        self.assertTrue("Degraded" in status or "Normal" in status,
                        '执行失败:' + text)
        self.assertEqual('off', param, '执行失败:' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
